package com.lovo.roomProject.system.dao.impl;

import com.lovo.roomProject.dao.BaseDAO;
import com.lovo.roomProject.pojo.CutPageBean;
import com.lovo.roomProject.pojo.UserDO;
import com.lovo.roomProject.system.dao.UserDAO;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDAOImpl extends BaseDAO implements UserDAO {
    private UserDO fillUser() throws SQLException {
        UserDO userDO = new UserDO();
        userDO.setId(rs.getInt("u_id"));
        userDO.setUserName(rs.getString("u_user_name"));
        userDO.setPassword(rs.getString("u_password"));
        userDO.setRealName(rs.getString("u_real_name"));
        userDO.setGrade(rs.getString("u_grade"));
        return userDO;
    }

    @Override
    public List listUser() {
        this.getConnection();
        String sql = "select * from t_user";
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            rs = statement.executeQuery();
            while (rs.next()) {
                UserDO userDO = fillUser();
                list.add(userDO);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }
        return list;
    }

    @Override
    public CutPageBean listUserByPage(int current) {
        this.getConnection();
        String selectSql = "select * ";
        String countSql = "select count(*) cnt ";
        String fromSql = "from t_user ";
        String limitSql = "limit ?,?";
        CutPageBean cutPageBean = new CutPageBean();
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(selectSql + fromSql + limitSql);
            statement.setObject(1, (current - 1) * CutPageBean.PAGESIZE);
            statement.setObject(2, CutPageBean.PAGESIZE);
            rs = statement.executeQuery();
            while (rs.next()) {
                UserDO userDO = fillUser();
                list.add(userDO);
            }
            statement = con.prepareStatement(countSql + fromSql);
            rs = statement.executeQuery();
            rs.next();
            cutPageBean.setCount(rs.getInt("cnt"));
            cutPageBean.setList(list);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }
        return cutPageBean;
    }

    @Override
    public UserDO getUserById(int id) {
        this.getConnection();
        String sql = "select * from t_user where u_id=?";
        UserDO userDO = null;
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, id);
            rs = statement.executeQuery();
            if (rs.next()) {
                userDO = fillUser();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }
        return userDO;
    }

    @Override
    public UserDO getUserByNameAndPassword(String userName, String password) {
        this.getConnection();
//        String sql = "select * from t_user where u_user_name=? and u_password=?";
        String selectSql = "select * ";
        String fromSql = "from t_user ";
        String dynamicSql = "where 1=1 ";
        if (userName!=null){
            dynamicSql+="and u_user_name='"+userName+"' ";
        }
        if (password!=null){
            dynamicSql+="and u_password='"+password+"' ";
        }
        UserDO userDO = null;
        try {
            statement = con.prepareStatement(selectSql+fromSql+dynamicSql);
//            statement.setObject(1, userName);
//            statement.setObject(2, password);
            rs = statement.executeQuery();
            if (rs.next()) {
                userDO = fillUser();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }
        return userDO;
    }

    @Override
    public void insert(UserDO userDO) {
        this.getConnection();
        String sql = "insert into t_user(u_user_name,u_real_name,u_grade) " +
                "value (?,?,?)";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, userDO.getUserName());
            statement.setObject(2, userDO.getRealName());
            statement.setObject(3, userDO.getGrade());
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }
    }

    @Override
    public void delete(int id) {
        this.getConnection();
        String sql = "delete from t_user where u_id=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, id);
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }
    }

    @Override
    public void update(UserDO userDO) {
        this.getConnection();
        String sql = "update t_user set u_password=?,u_grade=? where u_id=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, userDO.getPassword());
            statement.setObject(2, userDO.getGrade());
            statement.setObject(3, userDO.getId());
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }
    }
}
